﻿Option Strict On
Option Explicit On
Option Infer On

Imports System
Imports System.Linq
Imports System.Collections.Generic
Imports GenDotNet.TemplateSupport.StandardAttributeExtensions
Imports GenDotNet.Task.Common
Imports GenDotNet.StoredProcModel
Imports <xmlns:kd="http://kadgen.com/Special.xsd">
Imports <xmlns:orm="http://kadgen.com/KADORM.xsd">
Imports System.Xml.Linq
Imports GenDotNet.Model.Common
Imports GenDotNet.TemplateSupport.Strings
Imports GenDotNet.TemplateSupport.SqlServer


Public MustInherit Class SqlBase
   Inherits TemplateBase

   Protected MustOverride Function GenerateContents() As String
   Protected MustOverride ReadOnly Property StoredProcedureName() As String

   Private mEntityData As IEntityData

#Region "Overrides"

   Protected Overrides Function GetFileName(ByVal current As IBaseData) As String
      mEntityData = CType(current, IEntityData)
      Return StoredProcedureName & ".sql"
   End Function

   Protected Overrides Function GenerateFile(ByVal current As IBaseData) As String
      SetStrings()
      mEntityData = CType(current, IEntityData)
      Return _
<code>
   <%= OutputProcedureOpening() %>
   <%= Me.GenerateContents() %>
   <%= OutputClose() %>
</code>.Value
   End Function

#End Region

   Protected Overridable Sub SetStrings()
      Strings.andString = "AND"
      Strings.orString = "OR"
      Strings.nullString = "NULL"
      Strings.falseString = "0"
      Strings.trueString = "1"
   End Sub

   Public ReadOnly Property EntityData() As IEntityData
      Get
         Return mEntityData
      End Get
   End Property

   'Public Overridable Function WhereClause() As String
   '   Return String.Empty
   'End Function

   Protected Overridable ReadOnly Property IsMultiStatement() As Boolean
      Get
         Return False
      End Get
   End Property


   Protected Overridable Function OutputRetrieve( _
            ByVal isFirst As Boolean, _
            ByVal recSet As IRecordsetData) _
            As String
      Return OutputRetrieve(isFirst, String.Empty, recSet)
   End Function

   Protected Overridable Function OutputRetrieve( _
            ByVal isFirst As Boolean, _
            ByVal childJoin As String, _
            ByVal recSet As IRecordsetData) _
            As String
      Return OutputRetrieve(recSet, childJoin, (isFirst AndAlso recSet.IsDistinct))
   End Function

   Protected Overridable Function OutputRetrieve( _
            ByVal recSet As IRecordsetData, _
            ByVal childJoin As String, _
            ByVal isDistinct As Boolean) _
            As String
      Return _
<code>
      SELECT <%= If(isDistinct, " DISTINCT ", String.Empty) %>
   <%= OutputColumns(recSet.Columns) %>
   <%= OutputTablesAndJoins(recSet.MainTable, childJoin, recSet.Joins) %>
   <%= OutputWhereClause(EntityData.Recordset.PrimaryKeyColumns) %>
   <%= OutputOrderBies(recSet.OrderBies) %>;
</code>.Value
   End Function

   Public Overridable Function OutputParameterDeclarations(ByVal columns As IEnumerable(Of IColumnData)) As String
      Return "(" & (From column In columns Select _
       <code>
          @<%= column.ColumnAlias & TypeDeclaration(column) %>
          <%= If(column.IsAutoIncrement, " OUTPUT ", String.Empty) %>
          <%= If(column.AllowNulls, " = null ", String.Empty) %>
       </code>).ConcatenateWithComma() & ")"
   End Function

   Private Function TypeDeclaration(ByVal column As IColumnData) As String
      Return _
<code><%= space %><%= column.SQLType %>
   <%= If(((column.MaxLength > 0) And (column.MaxLength < Int32.MaxValue)), _
      "(" & column.MaxLength & ")", String.Empty) %>
</code>.Value
   End Function
   'Public Function OutputParameterColumns() As String
   '   Dim params = SPData.Parameters
   '   Return (From param In params Select _
   '   <code>
   '      <%= nws %>[<%= param.ColumnName %>]
   '   </code>).ConcatenateWithComma()
   'End Function

   'Public Function OutputParameterList() As String
   '   Dim params = SPData.Parameters
   '   Return (From param In params Select _
   '   <code>
   '      <%= nws %>@<%= param.Name %>
   '   </code>).ConcatenateWithComma()
   'End Function

   Public Function OutputWhereClause(ByVal columns As IEnumerable(Of IColumnData)) As String
      Dim whereClause = Me.WhereClause(columns)
      If String.IsNullOrEmpty(whereClause) Then
         Return String.Empty
      End If
      Return _
<code>
         WHERE <%= whereClause %>
</code>.Value
   End Function

   Protected Overridable Function WhereClause(ByVal columns As IEnumerable(Of IColumnData)) As String
      Return (From column In columns Select _
                 <code>@<%= column.ColumnAlias %> = <%= FullNameWithColumn(column.Table, column) %>
                 </code>).ConcatenateWith(andString.WithSpaceAround)
   End Function

   Public Overridable Function OutputProcedureOpening() As String
      Return <code>
                <%= SetSystem(True) %>
                <%= OutputOpening(StoredProcedureName, True) %>
CREATE PROCEDURE <%= StoredProcedureName %>
                <%= OutputParameterDeclarations(ParameterColumns) %>
      AS
             </code>.Value

   End Function

   Protected Overridable ReadOnly Property ParameterColumns() As IEnumerable(Of IColumnData)
      Get
         Return EntityData.Recordset.PrimaryKeyColumns
      End Get
   End Property

   Public Overridable Function OutputOpening(ByVal name As String, ByVal isStoredProcedure As Boolean) As String
      Return _
<code>
   <%= DeleteExisting(name, isStoredProcedure) %>
/* This stored proc was generated, please do not change */
/* Entity:        <%= EntityData.Name %> */
/* Entity Table:  <%= EntityData.Recordset.MainTable.Name %> */
/* Template Name: <%= TemplateName %> */
/* <%= If(isStoredProcedure, "Stored Procedure:  ", "Function:       ") %><%= name %> */
</code>.Value
   End Function

   Public Overridable Function SetSystem(ByVal setOn As Boolean) As String
      If setOn Then
         Return _
   <code>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
</code>.Value
      Else
         Return _
<code>
SET QUOTED_IDENTIFIER OFF 
         GO
SET ANSI_NULLS ON 
         GO
</code>.Value
      End If
   End Function

   Public Overridable Function DeleteExisting(ByVal objectName As String, ByVal isStoredProcedure As Boolean) As String
      Return _
<code>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[<%= objectName %>]') and OBJECTPROPERTY(id, N'<%= If(isStoredProcedure, "IsProcedure", "IsTableFunction") %>') = 1)
drop <%= If(isStoredProcedure, "Procedure", "Function") %> [dbo].[<%= objectName %>]
GO
</code>.Value
   End Function

   Public Function OutputClose() As String
      If Not IsMultiStatement Then
         Return _
   <code>
      RETURN (@@ERROR);
GO
      <%= SetSystem(False) %>
   </code>.Value
      Else
         Return _
  <code>
     <%= SetSystem(False) %>
  </code>.Value
      End If
   End Function

   Public Overridable Function OutputTablesAndJoins( _
            ByVal table As ITableData, _
            ByVal childJoin As String, _
            ByVal joins As IEnumerable(Of IJoinData)) _
            As String
      Return _
<code> FROM <%= FullName(table) %><%= nl %>
   <%= childJoin %>
   <%= From item In joins Select _
      <code>
         <%= item.JoinType.Output %> JOIN <%= FullNameAliased(item.ChildTable, item.ChildAlias) %>
            ON <%= (From joinKey In item.JoinKeys Select _
                  <code><%= FullNameWithColumn(item.ParentTable, joinKey.ParentColumnName) %> = <%= nws %>
                     <%= FullNameWithColumn(item.ChildTable, joinKey.ChildColumnName) %></code>).ConcatenateWithNewLine() %>
      </code> %>
</code>.Value

   End Function

   Public Overridable Function OutputColumns(ByVal columns As IEnumerable(Of IColumnData)) As String
      Return (From column In columns _
              Select _
              <code>
                 <%= FullNameWithColumnAndAlias(column.Table, column.Name, column.ColumnAlias) %>
              </code>).ConcatenateWithCommaNewLine()
   End Function

   Public Function OutputOrderBies(ByVal orderBies As IEnumerable(Of IOrderByData)) As String
      If orderBies.Count = 0 Then
         Return String.Empty
      End If
      Return _
<code>
         ORDER BY <%= (From orderBy In orderBies Select _
                     <code>
                        <%= FullName(orderBy.Column.Table) %>.[<%= orderBy.Column.ColumnAlias %>] <%= orderBy.Direction.Output %>
                     </code>).ConcatenateWithComma() %>
</code>.Value
   End Function

   Public Function OutputColumnNames(ByVal columns As IEnumerable(Of IColumnData)) As String
      Return (From column In columns Select column.Name.EscapeName).ConcatenateWithCommaNewLine
   End Function

   Public Function OutputColumnValues(ByVal columns As IEnumerable(Of IColumnData)) As String
      Return (From column In columns Select "@" & column.ColumnAlias).ConcatenateWithCommaNewLine
   End Function

   Public Function OutputOpenFunction( _
            ByVal functionName As String, _
                       ByVal ParamArray paramColumns() As IColumnData) _
            As String
      Return _
<code>
   <%= OutputOpening(functionName, False) %>
CREATE FUNCTION <%= functionName %>
   <%= OutputParameterDeclarations(paramColumns) %>
</code>.Value
   End Function

   Public Function OutputCloseFunction() As String
      Return String.Empty
   End Function

   Public Function OutputColumnDeclaration(ByVal columns As IEnumerable(Of IColumnData)) As String
      Return (From column In columns _
            Select EscapedName(column.ColumnAlias) & TypeDeclaration(column)).ConcatenateWithCommaNewLine
   End Function

#Region "Language specific overrides"
   Protected MustOverride Function EscapedName(ByVal name As String) As String
   Protected MustOverride Function FullName(ByVal table As ITableData) As String
   Protected MustOverride Function FullNameAliased(ByVal table As ITableData, ByVal aliasString As String) As String
   Protected MustOverride Function FullNameWithColumn(ByVal table As ITableData, ByVal column As IColumnData) As String
   Protected MustOverride Function FullNameWithColumn(ByVal table As ITableData, ByVal columnName As String) As String
   Protected MustOverride Function FullNameWithColumnAndAlias(ByVal table As ITableData, ByVal columnName As String, ByVal aliasString As String) As String
   Protected MustOverride Function Aliased(ByVal name As String, ByVal aliasString As String) As String
   Protected MustOverride Function AliasClause(ByVal name As String, ByVal aliasString As String) As String
#End Region

   'Public Function OptionalAlias(ByVal name As String, ByVal itemAlias As String) As String
   '   If String.IsNullOrEmpty(itemAlias) OrElse name.EqualsIgnoreCase(itemAlias) Then
   '      Return String.Empty
   '   End If
   '   Return " As " & itemAlias
   'End Function

   'Protected Overridable Function OutputRetrieveSp_SelectFromTemp(ByVal tempTableName As String) As String
   '   Return <code>
   '             SELECT * FROM <%= tempTableName %><%= nl %>
   '          </code>.Value
   'End Function

   'Protected Overridable Function GetTempTableName(ByVal objectName As String) As String
   '   Return "#temp" & objectName
   'End Function

   'Protected Overridable Function GetColumns(ByVal recSet As XElement) As IEnumerable(Of XElement)
   '   Return From x In recSet...<orm:TableColumn> _
   '          Where String.IsNullOrEmpty(x.@DependentColumnName) _
   '          Or x.@IsPrimaryKey = "true" _
   '          And Not x.@JoinBaseRootKey = "true"

   'End Function

   'Protected Overridable Function GetChildStoredProcName( _
   '         ByVal SpSet As XElement, _
   '         ByVal childspSet As XElement) As String
   '   If childspSet Is Nothing Then
   '      Return String.Empty
   '   End If
   '   Return "SelectChildren_" & SpSet.@Name & "_" & childspSet.@TableName
   'End Function

   'Protected Overridable Function ColumnSPListTableName(ByVal item As XElement) As String
   '   Dim tableName = String.Empty
   '   Select Case item.Name.LocalName
   '      Case "SetSelectColumn"
   '         tableName = item.@TableName
   '      Case "BuildColumn"
   '         tableName = item.Ancestors(buildTable).@RightAlias
   '      Case Else
   '         tableName = item.@TableAlias
   '   End Select
   '   Return tableName
   'End Function

   'Protected Overridable Function OutputORMMoreJoins(ByVal fKeyPosition As Int32) As String
   '   ' Only used for Retrieve children
   '   Return String.Empty
   'End Function

   'Protected Overridable Function OutputRetrieveSP_WhereClause(ByVal whereClause As String) As String
   '   If String.IsNullOrEmpty(whereClause) Then
   '      Return " WHERE " & whereClause
   '   End If
   '   Return String.Empty
   'End Function

   'Protected Overridable Function GetTableName(ByVal recset As XElement) As String
   '   Return (From table In recset...<orm:BuildTable> Take 1 Select table.@OriginalName).FirstOrDefault
   'End Function

   'Protected Overridable Function GetBuildJoin(ByVal recSet As XElement) As String
   '   Return _
   '<code>
   '   <%= From ormJoin In recSet...<orm:Join> _
   '      Let rightTableDeclaration = "[" & ormJoin.@RightOriginalName & "]" & If(String.IsNullOrEmpty(ormJoin.@RightAlias), String.Empty, " As " & ormJoin.@RightAlias) _
   '      Let rightTable = GetBuildJoinRightTable(ormJoin) _
   '      Let leftTable = GetBuildJoinLeftTable(ormJoin) _
   '      Select _
   '      <code>[<%= leftTable %>] <%= ormJoin.@Type %> JOIN <%= rightTableDeclaration %>
   '               ON <%= From joinKey In ormJoin...<orm:JoinKey> _
   '                     Select _
   '                     <code>
   '                        <%= "" %>[<%= leftTable %>].[<%= joinKey.@LeftColumn %>] = [<%= rightTable %>].[<%= joinKey.@RightColumn %>]
   '            </code> %>
   '      </code>.Value %>
   '</code>.Value
   'End Function

   'Protected Overridable Function GetBuildJoinLeftTable(ByVal join As XElement) As String
   '   Return join.@LeftOriginalName
   'End Function

   'Protected Overridable Function GetBuildJoinRightTable(ByVal join As XElement) As String
   '   Return If(String.IsNullOrEmpty(join.@RightAlias), join.@RightOriginalName, join.@RightAlias)
   'End Function

   'Protected Overridable Function GetFromClauseTable( _
   '         ByVal tableName As String, _
   '         ByVal tableAlias As String) _
   '         As String
   '   If tableName = tableAlias Then
   '      Return "[" & tableName & "]"
   '   Else
   '      Return "[" & tableName & "] As " & tableAlias
   '   End If
   'End Function

   'Protected Overrides Function GetFileName(ByVal element As System.Xml.Linq.XElement) As String

   'End Function

   'Public Overrides ReadOnly Property Id() As System.Guid
   '   Get

   '   End Get
   'End Property
End Class
